<?php
/**
 * Created by PhpStorm.
 * User: JSQ-SL
 * Date: 2018/10/19
 * Time: 16:16
 */

namespace app\index\table;

use app\index\controller\Login;
use app\index\service\ExcelService;
use think\Db;

class FurnTable extends BaseTable
{
    protected static $table = 'FURN_INFO';
    protected static $pk = 'FURN_NO';
    protected static $configTable = 'FURN_CONFIG';

    static public function table(){
        $sql = "(
        select 
        f.FURN_NO
        ,f.FURN_NAME
        ,to_char(f.ENTER_TIME,'yyyy-mm-dd') ENTER_TIME
        ,f.FURNTYPE_NO
        ,d11.FACITYPE
        ,f.DEPART_ID
        ,d1.DEPART
        ,d2.COLLEGE_ID
        ,d2.COLLEGE
        ,f.SPEC
        ,f.MODEL
        ,f.PRICE
        ,cast(f.FACTORY as varchar2(80)) FACTORY
        ,f.COUNTRY_ID
        ,d3.COUNTRY_CNNAME COUNTRY
        ,f.FACTORY_NO
        ,to_char(f.FACTORY_DATE,'yyyy-mm-dd') FACTORY_DATE
        ,to_char(f.GET_DATE,'yyyy-mm-dd') GET_DATE
        ,f.STATE_ID
        ,d4.STATE
        ,f.GET_PERSON
        ,f.CHECK_PERSON
        ,f.CHARGE_PERSON
        ,f.REMARK
        ,f.INPUT_PERSON
        ,d5.NAME INPUT_NAME
        ,to_char(f.INPUT_TIME,'yyyy-mm-dd') INPUT_TIME
        ,f.GB
        ,d6.NAME GB_NAME
        ,f.ZJLY
        ,d7.ZIJIN
        ,f.ZJLY_DETAIL
        ,f.BELONG_ID
        ,d8.BELONG
        ,f.ASSET_TYPE_ID
        ,d9.CNAME94 ASSET_TYPE
        ,f.BRAND
        ,f.PLACE
        ,f.REMARK2
        ,f.ID
        ,f.NOTICE_NO
        ,f.MUTI_BUY_MARK
        ,f.SUBMIT_MARK
        ,f.USEDIRECTION_ID
        ,d10.USEDIRECTION
        ,f.DEPRECIATION_TYPE
        ,f.MIN_SERVICE_LIFE
        ,f.SERVICE_LIFE
        ,f.UNUSED_LIFE
        ,f.DEPRECIATION_THIS_MONTH
        ,f.ACCUMULATIVE_DEPRECIATION
        ,f.CURRENT_VALUE
        ,f.LAST_DEPRECIATION_DATE
        ,f.FINANCE_DEPARTMENT
        ,f.FINANCE_INPUT_DATE
        from app_asset.furn_info f
        left join app_asset.DICT_SB_DEPARTMENT d1 on d1.depart_id=f.depart_id
        left join app_asset.DICT_COLLEGE d2 on d2.COLLEGE_ID=d1.DEPART_BELONG
        left join app_asset.DICT_COUNTRY d3 on d3.COUNTRY_ID=f.COUNTRY_ID
        left join app_asset.FURN_DICT_STATE d4 on d4.STATE_ID=f.STATE_ID
        left join app_asset.EMPLOYEE d5 on d5.STAFF_ID=f.INPUT_PERSON
        left join app_asset.FURN_DICT_GB d6 on d6.GBCODE=f.GB
        left join app_asset.DICT_FURN_ZIJIN d7 on d7.ID=f.ZJLY
        left join app_asset.ASSET_DICT_BELONG d8 on d8.BELONG_ID=f.BELONG_ID
        left join app_asset.ASSET_DICT_TYPE94TO2010 d9 on d9.ccode94=f.asset_type_id
        left join app_asset.ASSET_DICT_USEDIRECTION d10 on d10.USEDIRECTION_ID=f.USEDIRECTION_ID
        left join app_asset.DICT_FURN_FACITYPE d11 on d11.FACITYPE_ID=f.FURNTYPE_NO
        )";
        return Db::table($sql);
    }

    static public function add($data){
        $data['FURN_NO'] = static::getNextId();
        $data['INPUT_PERSON'] = Login::getUserId();
        $data['INPUT_TIME'] = date('Y-m-d');

        self::toDate($data,'ENTER_TIME');
        self::toDate($data,'FACTORY_DATE');
        self::toDate($data,'GET_DATE');
        self::toDate($data,'INPUT_TIME');
        Db::table(static::$table)->insert($data);
        return $data['FURN_NO'];
    }

    static public function addImport($data){
        $data['INPUT_PERSON'] = Login::getUserId();
        $data['INPUT_TIME'] = date('Y-m-d');

        self::toDate($data,'ENTER_TIME');
        self::toDate($data,'FACTORY_DATE');
        self::toDate($data,'GET_DATE');
        self::toDate($data,'INPUT_TIME');
        Db::table(static::$table)->insert($data);
    }

    static public function update($where,$data){
        self::toDate($data,'ENTER_TIME');
        self::toDate($data,'FACTORY_DATE');
        self::toDate($data,'GET_DATE');
        Db::table(static::$table)->where($where)->update($data);
    }

    static public function delete($where){
        return Db::table(static::$table)->where($where)->delete();
    }

    public static function toDate(&$data,$key){
        if(array_key_exists($key,$data)){
            $value = $data[$key];
            if(empty($value)){
                $data[$key] = null;
            }
            else{
                $data[$key] = Db::raw("to_date('$data[$key]','yyyy-mm-dd hh24:mi:ss')");
            }
        }
    }

    public static function getConfigPrint(){
        return Db::table(static::$configTable)->where(['PRINT_FLAG'=>'1'])->order('ID ASC')->select();
    }

    public static function getNextId(){
        $year = date('Y');
        $numMaxRecord = Db::table(static::$table)
            ->field('max(SUBSTR(FURN_NO,5,6)) as NUM')
            ->where([['SUBSTR(FURN_NO,1,4)','=',$year]])
            ->fetchSql(true)
            ->find();
        $numMaxRecord = Db::query($numMaxRecord);
        $num = $numMaxRecord[0]['NUM'];
        if ($num == NULL) {
            $num = 0;
        }
        $num++;
        return $year.sprintf("%06d", $num);
    }

    public static function exportExcel($data,$fileName){

        $fieldList = [
            ['title'=>'家具编号','field'=>'FURN_NO']
            ,['title'=>'家具名称','field'=>'FURN_NAME']
            ,['title'=>'入账时间','field'=>'ENTER_TIME']
            ,['title'=>'分类号','field'=>'FURNTYPE_NO']
            ,['title'=>'分类名称','field'=>'FACITYPE']
            ,['title'=>'使用部门','field'=>'DEPART']
            ,['title'=>'所在学院','field'=>'COLLEGE']
            ,['title'=>'技术指标','field'=>'SPEC']
            ,['title'=>'型号','field'=>'MODEL']
            ,['title'=>'价格','field'=>'PRICE']
            ,['title'=>'厂商','field'=>'FACTORY']
            ,['title'=>'国别','field'=>'COUNTRY']
            ,['title'=>'出厂号','field'=>'FACTORY_NO']
            ,['title'=>'出厂日期','field'=>'FACTORY_DATE']
            ,['title'=>'购置日期','field'=>'GET_DATE']
            ,['title'=>'状态','field'=>'STATE']
            ,['title'=>'领用人','field'=>'GET_PERSON']
            ,['title'=>'经手人','field'=>'CHECK_PERSON']
            ,['title'=>'负责人','field'=>'CHARGE_PERSON']
            ,['title'=>'备注','field'=>'REMARK']
            ,['title'=>'制单人','field'=>'INPUT_NAME']
            ,['title'=>'制单时间','field'=>'INPUT_TIME']
            ,['title'=>'国标','field'=>'GB']
            ,['title'=>'国标名称','field'=>'GB_NAME']
            ,['title'=>'经费来源','field'=>'ZIJIN']
            ,['title'=>'来源明细','field'=>'ZJLY_DETAIL']
            ,['title'=>'隶属单位','field'=>'BELONG']
            ,['title'=>'国有资产分类号','field'=>'ASSET_TYPE_ID']
            ,['title'=>'国有资产分类','field'=>'ASSET_TYPE']
            ,['title'=>'品牌','field'=>'BRAND']
            ,['title'=>'存放地点','field'=>'PLACE']
            ,['title'=>'备注2','field'=>'REMARK2']
            ,['title'=>'发票号','field'=>'NOTICE_NO']
            ,['title'=>'是否批量采购','field'=>'MUTI_BUY_MARK']
            ,['title'=>'是否提交','field'=>'SUBMIT_MARK']
        ];

        $excel = new ExcelService();
        $excel->exportExcel($data,$fileName,$fieldList);
    }

    const FURN_FIELD_LIST = [
        "FURN_NO","FURN_NAME","ENTER_TIME","FURNTYPE_NO","DEPART_ID","SPEC","MODEL","PRICE","FACTORY","COUNTRY_ID","FACTORY_NO","FACTORY_DATE","GET_DATE","STATE_ID","GET_PERSON","CHECK_PERSON","CHARGE_PERSON","REMARK","INPUT_PERSON","INPUT_TIME","GB","ZJLY","ZJLY_DETAIL","BELONG_ID","ASSET_TYPE_ID","ASSET_TYPE_ID","BRAND","PLACE","REMARK2","NOTICE_NO","MUTI_BUY_MARK","SUBMIT_MARK",
        ];

    public static function getSessionConditions() {
        $condition = null;
        $listCondition = Login::getListConditionFurn();
        if (!empty($listCondition['FURN_NO'])) {
            $value = $listCondition['FURN_NO'];
            $condition[] = ['f.FURN_NO','like',"$value%"];
        }
        if (!empty($listCondition['FURN_NAME'])) {
            $value = $listCondition['FURN_NAME'];
            $condition[] = ['f.FURN_NAME','like',"%$value%"];
        }
        if (!empty($listCondition['FURNTYPE_NO'])) {
            $value = $listCondition['FURNTYPE_NO'];
            $condition[] = ['f.FURNTYPE_NO','=',$value];
        }
        if (!empty($listCondition['ASSET_TYPE_ID'])) {
            $value = $listCondition['ASSET_TYPE_ID'];
            $condition[] = ['f.ASSET_TYPE_ID','=',$value];
        }
        if (!empty($listCondition['GB'])) {
            $value = $listCondition['GB'];
            $condition[] = ['f.GB','=',$value];
        }
        if (!empty($listCondition['STATE_ID'] || $listCondition['STATE_ID']==='00')) {
            $value = $listCondition['STATE_ID'];
            $condition[] = ['f.STATE_ID','=',$value];
        }
        if (!empty($listCondition['BELONG_ID']) || $listCondition['BELONG_ID']==='000') {
            $value = $listCondition['BELONG_ID'];
            $condition[] = ['f.BELONG_ID','=',$value];
        }
        if (!empty($listCondition['DEPART_ID'])) {
            $value = $listCondition['DEPART_ID'];
            $condition[] = ['f.DEPART_ID','=',$value];
        }
        if (!empty($listCondition['COUNTRY_ID'])) {
            $value = $listCondition['COUNTRY_ID'];
            $condition[] = ['f.COUNTRY_ID','=',$value];
        }
        if (!empty($listCondition['MUTI_BUY_MARK']) || $listCondition['MUTI_BUY_MARK']==='0') {
            $value = $listCondition['MUTI_BUY_MARK'];
            $condition[] = ['f.MUTI_BUY_MARK','=',$value];
        }
        if (!empty($listCondition['BRAND'])) {
            $value = $listCondition['BRAND'];
            $condition[] = ['f.BRAND','like',"%$value%"];
        }
        if (!empty($listCondition['MODEL'])) {
            $value = $listCondition['MODEL'];
            $condition[] = ['f.MODEL','like',"%$value%"];
        }
        if (!empty($listCondition['FACTORY'])) {
            $value = $listCondition['FACTORY'];
            $condition[] = ['f.FACTORY','like',"%$value%"];
        }
        if (!empty($listCondition['PRICE_START'])) {
            $value = $listCondition['PRICE_START'];
            $condition[] = ['f.PRICE','>=',$value];
        }
        if (!empty($listCondition['PRICE_END'])) {
            $value = $listCondition['PRICE_END'];
            $condition[] = ['f.PRICE','<=',$value];
        }
        if (!empty($listCondition['FACTORY_DATE_START'])) {
            $value = Db::raw("to_date('{$listCondition['FACTORY_DATE_START']}','yyyy-mm-dd')");
            $condition[] = ['f.FACTORY_DATE','>=',$value];
        }
        if (!empty($listCondition['FACTORY_DATE_END'])) {
            $value = Db::raw("to_date('{$listCondition['FACTORY_DATE_END']}','yyyy-mm-dd')");
            $condition[] = ['f.FACTORY_DATE','<=',$value];
        }
        if (!empty($listCondition['GET_DATE_START'])) {
            $value = Db::raw("to_date('{$listCondition['GET_DATE_START']}','yyyy-mm-dd')");
            $condition[] = ['f.GET_DATE','>=',$value];
        }
        if (!empty($listCondition['GET_DATE_END'])) {
            $value = Db::raw("to_date('{$listCondition['GET_DATE_END']}','yyyy-mm-dd')");
            $condition[] = ['f.GET_DATE','<=',$value];
        }
        if (!empty($listCondition['ENTER_TIME_START'])) {
            $value = Db::raw("to_date('{$listCondition['ENTER_TIME_START']}','yyyy-mm-dd')");
            $condition[] = ['f.ENTER_TIME','>=',$value];
        }
        if (!empty($listCondition['ENTER_TIME_END'])) {
            $value = Db::raw("to_date('{$listCondition['ENTER_TIME_END']}','yyyy-mm-dd')");
            $condition[] = ['f.ENTER_TIME','<=',$value];
        }
        return $condition;
    }

}